using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Framework.Library.Components.Providers;
using System.Data.SqlClient;
using Framework.Library.Utilities.Database;

namespace Framework.Library.Components.Database
{   
    /// <summary>
    /// Sotred procedure class
    /// </summary>
    public class _StoredProcedure : Info
    {
        #region "Variables"
        /// <summary>
        /// Store procedure definition
        /// </summary>
        private SqlDataReader _Definition;
        /// <summary>
        /// Store procedure privileges
        /// </summary>
        private SqlDataReader _Privilegies;
        /// <summary>
        /// Store procedure columns
        /// </summary>
        private SqlDataReader _Columns;
        #endregion

        #region "Properties"
        /// <summary>
        /// Store procedure definition
        /// </summary>
        public SqlDataReader Definition
        {
            get
            {
                return _Definition;
            }
        }

        /// <summary>
        /// Store procedure privileges
        /// </summary>
        public SqlDataReader Privilegies
        {
            get
            {
                return _Privilegies;
            }
        }

        /// <summary>
        /// Store procedure columns
        /// </summary>
        public SqlDataReader Columns
        {
            get
            {
                return _Columns;
            }
        }
        #endregion

        /// <summary>
        /// Constructor
        /// </summary>
        public _StoredProcedure()
        {
        }
        /// <summary>
        /// Get all Store Procedure
        /// </summary>
        public DataTable GetStores()
        {
            DataTable dtCommands = DataAccessProvider.Instance().GetStores();
            return dtCommands;
        }
        /// <summary>
        /// Load procedure properties, afert setting the object ID
        /// </summary>
        public void LoadProperties()
        {
            string _sqlCommand = "SELECT name, USER_NAME(uid) FROM sysobjects WHERE id = " + ID.ToString();
            SqlDataReader myReader = DataAccessProvider.Instance().ReturnReader(_sqlCommand);
            while (myReader.Read())
            {
                this.Name = myReader.GetString(0);
                this.Owner = myReader.GetString(1);
            }
        }
        /// <summary>
        /// Gets stored procedures list with details
        /// </summary>
        /// <returns>Procedures list</returns>
        public DataTable GetStoredProcedures()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append(" SELECT DISTINCT sysobjects.id, USER_NAME(uid) as owner, name, USER_NAME(uid) + '.' + name as completeName, sysobjects.crdate, IsEncrypted = CASE syscomments.encrypted WHEN 0 THEN 'No' ELSE 'Yes' END");
            _sqlCommand.Append(" FROM sysobjects, syscomments ");
            _sqlCommand.Append(" WHERE (type='P' OR type='RF') AND sysobjects.id=syscomments.id ORDER BY owner,name");

            return DataAccessProvider.Instance().ReturnDataSet(_sqlCommand.ToString());
        }
        /// <summary>
        /// Set Definition Property
        /// </summary>
        public void SetDefinition()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("EXEC sp_helptext '" + this.GetObjectName().Replace("'", "''") + "'");
            _Definition = DataAccessProvider.Instance().ReturnReader(_sqlCommand.ToString());
        }
        /// <summary>
        /// Return the stored procedure privileges
        /// </summary>
        public DataTable GetPrivilegies()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("EXEC sp_helprotect '" + this.GetObjectName().Replace("'", "''") + "'");
            return DataAccessProvider.Instance().ReturnDataSet(_sqlCommand.ToString());
        }
        /// <summary>
        /// Stored procedure users
        /// </summary>
        public SqlDataReader Users()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append(" SELECT name FROM sysusers WHERE (issqluser=1 OR isntname=1) AND sid IS NOT NULL ");
            _sqlCommand.Append(" and name <> (SELECT DISTINCT USER_NAME(uid) as owner FROM sysobjects where id = object_id('" + this.GetObjectName().Replace("'", "''") + "'))");
            _sqlCommand.Append(" ORDER BY name");
            return DataAccessProvider.Instance().ReturnReader(_sqlCommand.ToString());
        }
        /// <summary>
        /// Return the procedure roles
        /// </summary>
        public SqlDataReader Roles()
        {
            StringBuilder _sqlCommand = new StringBuilder();
            _sqlCommand.Append("SELECT name FROM sysusers WHERE (issqlrole=1 OR isapprole=1) AND altuid <> 1");
            return DataAccessProvider.Instance().ReturnReader(_sqlCommand.ToString());
        }
        /// <summary>
        /// Drop stored procedure
        /// </summary>
        public void Drop()
        {
            string _sqlCommand = "DROP PROCEDURE " + this.GetObjectName();
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Rename the store procedure [owner].[name + date ticks]
        /// </summary>
        public void Rename()
        {
            string strNewName = "[" + this.Owner + "].[" + this.Name + DateTime.UtcNow.Ticks.ToString() + "]";
            Rename(strNewName);
        }
        /// <summary>
        /// Rename the stored procedure
        /// </summary>
        /// <param name="newName">New sotred procedure name</param>
        public void Rename(string newName)
        {
            //get DDL
            string _sqlCommand = "";
            SetDefinition();

            while (_Definition.Read())
            {
                _sqlCommand += _Definition.GetString(0).ToString();
            }

            //create the new proc

            _sqlCommand = _sqlCommand.Replace("CREATE PROCEDURE [" + this.Owner + "].[" + this.Name + "]", "CREATE PROCEDURE " + newName);
            _sqlCommand = _sqlCommand.Replace("CREATE PROCEDURE " + this.Owner + "." + this.Name, "CREATE PROCEDURE " + newName);

            Create(_sqlCommand);

            //grant privilegies
            SetPrivilegies();

            if (_Privilegies != null)
            {
                while (_Privilegies.Read())
                {
                    _sqlCommand = "GRANT EXECUTE ON " + newName + " TO " + _Privilegies.GetString(2).ToString();
                    DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
                }
            }
            //drop old proc
            Drop();
        }
        /// <summary>
        /// Grant procedure execution
        /// </summary>
        /// <param name="strUser">Database username</param>
        public void GrantExecute(string strUser)
        {
            string _sqlCommand = "GRANT EXECUTE ON " + this.GetObjectName() + " TO " + strUser;
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Revoke execute to a specific user
        /// </summary>
        /// <param name="strUser">Database user</param>
        public void RevokeExecute(string strUser)
        {
            string _sqlCommand = "REVOKE EXECUTE ON " + this.GetObjectName() + " FROM " + strUser;
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Create a new stored procedure
        /// </summary>
        public void Create(string _sqlCommand)
        {
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }
        /// <summary>
        /// Set Privilegies Property
        /// </summary>
        public void SetPrivilegies()
        {
            string _sqlCommand = "";
            _sqlCommand += "EXEC sp_helprotect '" + this.GetObjectName().Replace("'", "''") + "'";
            _Privilegies = DataAccessProvider.Instance().ReturnReader(_sqlCommand);
        }
        /// <summary>
        /// Set Columns Property
        /// </summary>
        public void SetColumns()
        {
            string _sqlCommand = "";
            _sqlCommand += "EXEC sp_sproc_columns N'" + this.Name.Replace("'", "''") + "', N'" + this.Owner.Replace("'", "''") + "', NULL, @ODBCVer = 3";
            _Columns = DataAccessProvider.Instance().ReturnReader(_sqlCommand);
        }
        /// <summary>
        /// Convert Database types to Oledb.Types
        /// </summary>
        /// <param name="dbType">database type</param>
        public SqlDbType ColumnType(string dbType)
        {
            SqlDbType myType;
            switch (dbType)
            {
                case "bigint":
                    myType = SqlDbType.BigInt;
                    break;
                case "binary":
                    myType = SqlDbType.Binary;
                    break;
                case "timestamp":
                    myType = SqlDbType.Binary;
                    break;
                case "bit":
                    myType = SqlDbType.Bit;
                    break;
                case "char":
                    myType = SqlDbType.Char;
                    break;
                case "money":
                    myType = SqlDbType.Money;
                    break;
                case "smallmoney":
                    myType = SqlDbType.SmallMoney;
                    break;
                case "datetime":
                    myType = SqlDbType.DateTime;
                    break;
                case "smalldatetime":
                    myType = SqlDbType.DateTime;
                    break;
                case "float":
                    myType = SqlDbType.Float;
                    break;
                case "uniqueidentifier":
                    myType = SqlDbType.UniqueIdentifier;
                    break;
                case "identity":
                    myType = SqlDbType.Int;
                    break;
                case "int":
                    myType = SqlDbType.Int;
                    break;
                case "image":
                    myType = SqlDbType.Image;
                    break;
                case "text":
                    myType = SqlDbType.NVarChar;
                    break;
                case "ntext":
                    myType = SqlDbType.NText;
                    break;
                case "decimal":
                    myType = SqlDbType.Decimal;
                    break;
                case "numeric":
                    myType = SqlDbType.Decimal;
                    break;
                case "real":
                    myType = SqlDbType.Real;
                    break;
                case "smallInt":
                    myType = SqlDbType.SmallInt;
                    break;
                case "tinyInt":
                    myType = SqlDbType.TinyInt;
                    break;
                case "varbinary":
                    myType = SqlDbType.VarBinary;
                    break;
                case "varchar":
                    myType = SqlDbType.VarChar;
                    break;
                case "sql_variant":
                    myType = SqlDbType.Variant;
                    break;
                case "nvarchar":
                    myType = SqlDbType.NVarChar;
                    break;
                case "nchar":
                    myType = SqlDbType.NChar;
                    break;
                default:
                    myType = SqlDbType.NVarChar;
                    break;
            }
            return myType;
        }
        /// <summary>
        /// Get Command name
        /// </summary>
        public SqlCommand GetCommand()
        {
            return DataAccessProvider.Instance().GetProcedureCommand(GetObjectName());
        }
        /// <summary>
        /// Execute a query command
        /// </summary>
        /// <param name="_sqlCommand">Query to execute</param>
        public void ExecuteQuery(string _sqlCommand)
        {
            DataAccessProvider.Instance().ExecuteCommand(_sqlCommand);
        }          
    }    
}
